import argparse
import csv
import os
import smtplib
import sqlite3
import sys
import zipfile
from configparser import ConfigParser
from email.header import decode_header
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from typing import Union
from loguru import logger
import imaplib
import email
import requests


def update():
    """
    检测当前版本是否为最新版
    """
    v = 'v1.0'
    releases_url = "https://gitee.com/api/v5/repos/zhycarge/collect_report/releases/latest"
    response = requests.get(releases_url)
    if response.status_code != 200:
        print("访问更新链接失败！")
        return
    data = response.json()
    latest_version = data["tag_name"]
    if latest_version != v:
        logger.info(
            f"当前版本不是最新版本，请考虑进行更新，更新链接如下:https://gitee.com/zhycarge/collect_report/releases/tag/{latest_version}")
    else:
        logger.info(f"您当前版本是{latest_version}，已经是最新版本")


def delete_report(report_id: str):
    """
    删除指定的报告
    :param report_id: 报告ID
    """
    ok = input(f"请确定！您即将删除{report_id}的报告提交记录，此操作无法挽回，请注意存储好必要信息，如确定请输入y(Y)，不确定请输入n(N)\n")
    if ok.lower() == 'n':
        return
    select_sql = "SELECT * FROM report where report_id = ? "
    cursor.execute(select_sql, (report_id,))
    report = cursor.fetchone()
    if report is None:
        logger.info(f"无法查询到指定报告，请确定您输入的报告ID'{report_id}'正确")
        return 0
    else:
        delete_sql = "DELETE FROM report WHERE report_id = ?"
        cursor.execute(delete_sql, (report_id,))
        conn.commit()
        logger.debug(f"已删除report表中的{report_id}记录")
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='{}'".format(report_id))
        result = cursor.fetchall()
        if not result:
            logger.debug("不存在指定表，无需删除")
        else:
            cursor.execute("DROP TABLE '{}'".format(report_id))
            conn.commit()
            logger.debug(f"已删除{report_id}表")
        logger.info(f"已删除报告编号为：{report_id}的所有提交记录")


def show_report():
    """
    显示现在的报告详情
    """
    cursor.execute("SELECT * FROM report")
    result = cursor.fetchall()
    logger.info(result)


def judge_report_sm(report_name: str) -> bool:
    """
    判断更改的报告名称是否相同
    :param report_name: 报告名称
    :return: 是否相同
    """
    select_sql = "select * from report where report_name = ? and status = '0'"
    cursor.execute(select_sql, (report_name,))
    rows = cursor.fetchall()
    if len(rows) > 0:
        return True
    else:
        return False


def change_report(report_id: str, report_name: str, status: str):
    """
    更改报告内容
    :param report_id: 报告ID
    :param report_name: 报告名称
    :param status: 报告当前状态
    """
    if judge_report_sm(report_name):
        logger.error(f"您的更改将会存在两个相同名称的报告，请关闭一个或指定另外一个名字")
        return
    sql = "UPDATE report SET report_name = ? ,status = ? WHERE report_id = ?"
    # 执行 SQL 语句
    cursor.execute(sql, (report_name, status, report_id))
    # 提交事务
    conn.commit()
    logger.info(f"已更新序号为：{report_id}的信息,现在其报告名称为：{report_name},状态为:{status}")


def add_report(report_id: str, report_name: str, status: str):
    """
    添加需要收集的报告
    :param report_id:需要收集的报告ID（后续作为其主键）
    :param report_name: 需要收集的报告名称（请确定这个名字，后续收集报告的时候需要学生填写）
    :param status: 收集报告状态（0代表正在收集，1代表已收集完成）
    """
    if judge_report_sm(report_name):
        logger.error(f"存在两个相同名称的报告，请关闭一个或指定另外一个名字")
        return
    sql = "INSERT INTO report (report_id, report_name, status) VALUES (?, ?, ?)"
    cursor.execute(sql, (report_id, report_name, status))
    conn.commit()
    logger.info(f"已添加序号为：{report_id},报告名称为：{report_name},状态为:{status}的报告信息")


def get_database_info(report_id: str):
    """
    获取指定报告的提交情况
    :param report_id: 表名称
    """
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='{}'".format(report_id))
    result = cursor.fetchall()
    if not result:
        logger.info(f"无法寻找到{report_id}的报告提交信息")
        return
    # 定义 SQL 语句
    sql = "SELECT id as '序号',st_id as '学号', st_name as '姓名', GROUP_CONCAT(report_id, ', ') as '提交实验报告' FROM '{}' group by st_id".format(
        report_id)
    # 执行 SQL 语句
    cursor.execute(sql)
    # 获取查询结果
    rows = cursor.fetchall()
    # 导出数据到 CSV 文件
    with open("{}.csv".format(report_id), "w", newline="") as f:
        writer = csv.writer(f)
        writer.writerow([i[0] for i in cursor.description])  # 写入表头
        writer.writerows(rows)
    logger.info(f"课程序号为：{report_id}的提交信息已经导出到指定文件中")


def judge_infos(table_name: str, st_id: str, st_name: str, report_id: str) -> bool:
    """
    判断学生是否提交相关报告
    :param table_name: 表名称
    :param st_id: 学生学号
    :param st_name: 学生名字
    :param report_id: 报告ID
    :return: (bool)是否存在相关提交信息
    """
    sql = "select * from '{}' where st_id = ? and st_name = ? and report_id = ?".format(table_name)
    cursor.execute(sql, (st_id, st_name, report_id,))
    a = cursor.fetchone()
    if a is None:
        return True
    else:
        return False


def insert_infos(report_name: str, st_id: str, st_name: str, report_id: str):
    """
    插入学生提交报告信息
    :param report_name: 报告名称
    :param st_id: 学生学号
    :param st_name: 学生姓名
    :param report_id: 报告ID
    """
    table_name = judge_tables(report_name)
    if table_name is None:
        logger.error(f"查询不到{report_name}的报告ID")
        return
    if judge_infos(table_name, st_id, st_name, report_id):
        sql = "INSERT INTO '{}' (st_id, st_name, report_id) VALUES (?, ?, ?)".format(table_name)
        cursor.execute(sql, (st_id, st_name, report_id))
        conn.commit()
        logger.debug(f"已在{table_name}中添加{st_id}-{st_name}的{report_id}")


def judge_tables(report_name: str) -> Union[None, str]:
    """
    判断是否存在正在收集的指定数据表，若存在返回报告ID
    :param report_name: 表名称
    :return: 若查询不到则返回None，若查询到则返回报告ID
    """
    cursor.execute(f"SELECT report_id FROM report WHERE report_name = ? and status = '0'",
                   (report_name,))
    a = cursor.fetchone()
    if a is not None:
        report_id = a[0]
    else:
        return None

    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='{}'".format(report_id))
    result = cursor.fetchall()
    if not result:
        create_tables(report_id)
    return report_id


def judege_folder(filepath: str):
    """
    判断是否存在所在路径，如不存在则自行创建
    :param filepath: 需要判断的路径
    """
    if not os.path.exists(filepath):
        os.makedirs(filepath)
        logger.debug(f"创建目录{filepath}")


def reduce_zip(filepath: str, st_id: str, st_name: str, report_name: str, sender_email: str):
    """
    处理压缩文件
    :param filepath: 压缩包文件路径
    :param st_id: 学号
    :param st_name: 姓名
    :param report_name: 报告名称
    :param sender_email: 发送者邮箱
    """
    report_id = judge_tables(report_name)
    if report_id is None:
        logger.debug(f"{st_id}-{st_name}的实验报告名称不正确")
        send_email(sender_email, "请检查你的实验报告名称是否正确", [st_id, st_name, report_name], 'error')
        return
    save_path = SettingConfig['report_download'] + f'{report_name}_{report_id}/'
    judege_folder(save_path)
    # 解压文件
    with zipfile.ZipFile(filepath, 'r') as zip_file:
        # 获取压缩包文件列表
        for file_info in zip_file.infolist():
            # 获取文件名和编码
            file_name = file_info.filename.encode("cp437").decode("gbk")
            insert_infos(report_name, st_id, st_name, os.path.splitext(file_name)[0])
            origin_file_name = file_info.filename
            zip_file.extract(origin_file_name, save_path + os.path.splitext(file_name)[0] + '/')
            if not os.path.isfile(save_path + os.path.splitext(file_name)[0] + '/' + f'{st_id}-{st_name}-{file_name}'):
                os.rename(save_path + os.path.splitext(file_name)[0] + '/' + origin_file_name,
                          save_path + os.path.splitext(file_name)[0] + '/' + f'{st_id}-{st_name}-{file_name}')
            else:
                os.remove(save_path + os.path.splitext(file_name)[0] + '/' + f'{st_id}-{st_name}-{file_name}')
                os.rename(save_path + os.path.splitext(file_name)[0] + '/' + origin_file_name,
                          save_path + os.path.splitext(file_name)[0] + '/' + f'{st_id}-{st_name}-{file_name}')
                logger.debug(f"已更新{st_id}-{st_name}的{report_name}的{os.path.splitext(file_name)[0]}")
    os.remove(filepath)
    send_email(sender_email, "您的邮件已收到", [st_id, st_name, report_name], 'success')
    logger.info(f"已添加{st_id}-{st_name}的{report_name}实验报告")


def MIME_decode(texts: str) -> str:
    """
    将MIME编码的文本转换为原始的文本和编码方式
    :param texts: 传入的MIME编码文本
    :return: 正常的编码文本
    """
    decoded_header = decode_header(texts)
    subject = ''
    for part in decoded_header:
        text, charset = part
        if charset:
            text = text.decode(charset)
        subject += text
    return subject


def send_email(send_user: str, subject: str, info: list, tag: str):
    """
    发送邮件
    :param send_user: 发送给谁
    :param subject: 主题
    :param info: 详细信息
    :param tag: 标签
    """
    msg = MIMEMultipart()
    msg['To'] = send_user
    msg['Subject'] = subject
    if tag == 'error':
        msg.attach(MIMEText(f"学号:{info[0]},姓名：{info[1]}：<br/>"
                            f"你好：你提交的{info[2]}因不符合{subject}要求被打回，请按格式整理好之后重新提交", 'html'))
    elif tag == 'success':
        msg.attach(MIMEText(f"学号:{info[0]},姓名：{info[1]}<br/>"
                            f"你好：<br/>你提交的{info[2]}已经记录完毕，特此回复", 'html'))
    # 发送回复电子邮件
    smtp = smtplib.SMTP_SSL(EmailConfig['smtp'], int(EmailConfig['smtp_port']))
    smtp.login(EmailConfig['email_user'], EmailConfig['password'])
    smtp.sendmail(EmailConfig['email_user'], send_user, msg.as_string())
    logger.info(f"已经给{info[0]}-{info[1]}发送了{subject}的邮件")


def create_tables(ids: str):
    """
    创建收集报告表
    :param ids: 表名
    """
    cursor.execute(f'''CREATE TABLE '{ids}' 
                  (id INTEGER PRIMARY KEY AUTOINCREMENT, st_id TEXT, st_name TEXT, report_id TEXT )''')
    conn.commit()
    logger.info(f"已创建{ids}的数据表")


def email_from(texts: str) -> tuple[str, str]:
    """
    获取邮件发件人信息
    :param texts: 原始发件人信息
    :return: 分割好的发件人信息
    """
    texts = texts.split("<")
    return str(MIME_decode(texts[0].strip()) + " <" + texts[1]), str(texts[1].split(">")[0])


def receive_email():
    """
    接收邮件
    """
    mail = imaplib.IMAP4_SSL(EmailConfig['imap'])
    mail.login(EmailConfig['email_user'], EmailConfig['password'])
    mail.select('inbox')

    # 搜索收件箱中的邮件
    try:
        status, email_ids = mail.search(None, 'ALL')
        # 将邮件ID列表拆分成单独的邮件ID
        email_ids = email_ids[0].split()
    except imaplib.IMAP4.error as e:
        logger.error(e)
        return
    # 遍历邮件ID列表，并获取每封邮件的主题
    for email_id in email_ids:
        sss = 0
        _, email_seen = mail.fetch(email_id, '(FLAGS)')
        _, email_data = mail.fetch(email_id, '(RFC822)')
        flags = email_seen[0].decode('utf-8')
        email_message = email.message_from_bytes(email_data[0][1])
        subject_all = MIME_decode(email_message['Subject'])
        from_email = email_message['from']
        if '\\Seen' in flags:
            logger.debug(f"“{subject_all}”该邮件已经被阅读，不再进行处理")
        else:
            subject = subject_all.split("-")
            if len(subject) != 3 and len(subject) != 1:
                logger.warning(f"{subject}邮件主题格式不符合要求，不进行读取")
                continue
            from_email = email_from(from_email)
            st_id = subject[0]
            st_name = subject[1]
            report_name = subject[2]

            # 遍历邮件中的各个部分

            for part in email_message.walk():
                # 如果是附件

                if part.get_content_disposition() == 'attachment':
                    sss = 1
                    # 获取附件文件名
                    filename = part.get_filename()
                    # 如果文件名不为空
                    if filename:
                        filename = MIME_decode(filename)
                        if filename[-3:] != 'zip':
                            logger.warning(f"邮件主题为{subject_all}的邮件附件格式不符合要求，已被打回")
                            send_email(from_email[1], "附件格式不符合", [st_id, st_name, report_name], 'error')
                        else:
                            # 在当前目录下创建一个与文件名相同的文件，并将附件内容写入该文件
                            with open(SettingConfig['report_download'] + filename, 'wb') as f:
                                f.write(part.get_payload(decode=True))

                            reduce_zip(SettingConfig['report_download'] + filename, st_id, st_name, report_name,
                                       from_email[1])

            if sss == 0:
                # 说明没有找到附件
                logger.warning(f"邮件主题为:{subject_all}的邮件未添加附件")
                send_email(from_email[1], "未添加附件", [st_id, st_name, report_name], 'error')
    # 关闭邮箱连接
    mail.close()
    mail.logout()


def parserr():
    parser = argparse.ArgumentParser()
    parser.add_argument('--runs', type=str,
                        help="运行的方式(请输入括号内数字即可):\n"
                             "【0】nomal 收取整个实验报告邮件\n"
                             "【1】show_reports 展示数据库中的所有报告信息\n"
                             "【2】change_report 更改数据库中的报告信息（需额外提供参数）\n"
                             "【3】get_info 获取指定报告的收集情况（需额外提供信息）\n"
                             "【4】add_reports 添加报告信息（需额外提供信息）\n"
                             "【5】update 检查程序是否需要更新（需额外提供信息）\n"
                             "【6】delete_report 删除指定报告（需额外提供信息）\n")
    parser.add_argument('--conf', type=str, default='./setting.ini', help='选择配置文件存放位置')
    parser.add_argument('--re_id', type=str, help='报告编号')
    parser.add_argument('--re_na', type=str, help='报告名称')
    parser.add_argument('--re_st', type=str, help='报告状态')

    return parser.parse_known_args()[0]


if __name__ == '__main__':
    opt = parserr()
    if opt.runs is None:
        opt.runs = input("请输入需要运行的方式(请输入括号内数字即可):\n"
                         "【0】nomal 收取整个实验报告邮件\n"
                         "【1】show_reports 展示数据库中的所有报告信息\n"
                         "【2】change_report 更改数据库中的报告信息（需额外提供参数）\n"
                         "【3】get_info 获取指定报告的收集情况（需额外提供信息）\n"
                         "【4】add_reports 添加报告信息（需额外提供信息）\n"
                         "【5】update 检查程序是否需要更新（需额外提供信息）\n"
                         "【6】delete_report 删除指定报告（需额外提供信息）\n").strip()
    # 配置文件读取
    cfg = ConfigParser()
    cfg.read(opt.conf, encoding='utf-8')
    EmailConfig = dict(cfg.items("email"))
    SettingConfig = dict(cfg.items("setting"))
    judege_folder(SettingConfig['report_download'])
    logger.remove()
    logger.add(sys.stderr, format="{time:YYYY-MM-DD HH:mm:ss} {level} {message}", level=SettingConfig['level'])
    if SettingConfig['level'] == 'DEBUG':
        logger.add('./runs.log', retention='3 days', rotation='5MB')

    # 数据库信息读取
    logger.debug("数据库正在初始化")
    db_file = SettingConfig['database_path']
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()
    if opt.runs is None:
        logger.error("未选择运行方式！")
        exit(-1)
    elif opt.runs == '1':
        show_report()
    elif opt.runs == '0':
        # 连接邮箱并获取收件箱信息
        receive_email()
    elif opt.runs == '4':
        if opt.re_id is None:
            opt.re_id = input("请输入课程序号:\n").strip()
        if opt.re_na is None:
            opt.re_na = input("请输入课程名称:\n").strip()
        if opt.re_st is None:
            opt.re_st = input("请输入课程状态:\n").strip()
        logger.debug(f"报告ID{opt.re_id}, 报告名称{opt.re_na}, 报告状态{opt.re_st}")
        if opt.re_id is None or opt.re_na is None or opt.re_st is None:
            logger.error("请补全必要参数！")
            exit(0)
        else:
            add_report(opt.re_id, opt.re_na, opt.re_st)
    elif opt.runs == '3':
        if opt.re_id is None:
            opt.re_id = input("请输入课程编号:\n").strip()
            if opt.re_id is None:
                logger.error("请输出正确内容！").strip()
                exit(-1)
            get_database_info(opt.re_id)
        else:
            get_database_info(opt.re_id)
    elif opt.runs == '2':
        if opt.re_id is None:
            opt.re_id = input("请输入课程序号:\n").strip()
        if opt.re_na is None:
            opt.re_na = input("请输入课程名称:\n").strip()
        if opt.re_st is None:
            opt.re_st = input("请输入课程状态:\n").strip()
        if opt.re_id is None or opt.re_na is None or opt.re_st is None:
            logger.error("请补全必要参数！")
            exit(0)
        else:
            change_report(opt.re_id, opt.re_na, opt.re_st)
    elif opt.runs == '5':
        update()
    elif opt.runs == '6':
        if opt.re_id is None:
            opt.re_id = input("请输入课程编号:\n").strip()
            if opt.re_id is None:
                logger.error("请输出正确内容！")
                exit(-1)
            delete_report(opt.re_id)
        else:
            delete_report(opt.re_id)

    cursor.close()
    conn.close()
    input("请输入任何键停止运行...")
